//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Copyright 2007 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   FACT
// AUTHOR           :   Jimmy chen 
// DATE CREATE      :   2007/12/17
// DESCRIPTION      :   a DAO file for the function, FACT
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2007/12/17   Jimmy chen      1.0                 source is generated.
//
//
//
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

package com.erp.app.common.attachment.dao;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import oracle.sql.BLOB;

import com.erp.app.common.attachment.beans.AttachmentVo;
import com.erp.app.common.attachment.pk.AttachmentPk;
import com.erp.framework.exceptions.CreateRecordException;
import com.erp.framework.exceptions.DeleteRecordException;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.exceptions.ModifyRecordException;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomBlob;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Jimmy chen 
* @version 1.0     2007/12/17
* @since           FrameWork 1.0
*
*/
public abstract class AttachmentBaseDao extends SimpleSearchMaintStyleDao {

	/**
	* Returns an array list of child VOs by the parent
	* @param         parent   the incoming parent VO
	* @param         conn     the database connection
	* @return        an array list of child VOs
	*
	* @author     Jimmy chen  
	* @since      Framework 1.0
	*/
	public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
		throws FindRecordException {
		return super.selectByParent(parent, this.getAttachmentVoClass(), conn);
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Jimmy chen 
	* @since      Framework 1.0
	*/
	protected String sqlInsert() {
		return null;
	}

	protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException {
	}

    public void insert(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException {
    	insertAttachment(bizModel, conn);
    }

	public void insertAttachment(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException {
		PreparedStatement pStmt = null;
        ResultSet rs = null;
        AttachmentVo vo = (AttachmentVo) bizModel;
        try {
    		String query = "INSERT INTO T_ATTACHMENT ("
    						+ "COMPANY_CODE, LINE_NO, ATTACHMENT_DESC, CONTENT_TYPE, ATTACHMENT"
    						+ ", FILE_NAME, CREATE_USER, CREATE_DATE, DC_IND) "
    						+ " VALUES("
    						+ "?, ?, ?, ?, EMPTY_BLOB()"
    						+ ", ?, ?, SYSDATE, 'NO'"
    						+ ")";
            pStmt = conn.prepareStatement(query);
    		int i = 1;
    		pStmt.setString(i++, vo.getCompanyCode());
    		pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
    		setNullableString(pStmt, i++, vo.getAttachmentDescription());
    		setNullableString(pStmt, i++, vo.getAttachmentContentType());
    		setNullableString(pStmt, i++, vo.getAttachmentFileName());
    		setNullableString(pStmt, i++, vo.getCreateUser());
            int count = pStmt.executeUpdate();
            if (count != 1)
                throw new SQLException("Unable to insert " + vo.getPrimaryKey());

            if(vo.getAttachment()!=null){
                query = "select ATTACHMENT from T_ATTACHMENT where COMPANY_CODE=? AND LINE_NO=?";
                pStmt = conn.prepareStatement(query);
        		pStmt.setString(1, vo.getCompanyCode());
        		pStmt.setInt(2, vo.getAttachmentAttachmentLineNo().intValue());
    			rs = pStmt.executeQuery();
    			if(rs.next()){
        			BLOB blob=(BLOB) rs.getBlob("ATTACHMENT");  
        			OutputStream out=blob.getBinaryOutputStream();  
        			try {
        				out.write(vo.getAttachment());
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
        			try {
        				out.close();
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
    			}else{
                    throw new SQLException("Unable to insert " + vo.getPrimaryKey());
    			}
            }
//			conn.commit();
        } catch (SQLException sqe) {
            throw new CreateRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }

    /**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Jimmy chen 
	* @since      Framework 1.0
	*/
	protected String sqlUpdate() {
		return null;
	}

	protected void setValuesForModify(PreparedStatement pStmt, Object obj)	throws SQLException {
	}
	
	public void update(SimpleBaseVo bizModel, Connection conn) throws ModifyRecordException {
		updateAttachment(bizModel, conn);
    }

    public void updateAttachment(SimpleBaseVo bizModel, Connection conn) throws ModifyRecordException {
    	PreparedStatement pStmt = null;
        ResultSet rs = null;
        AttachmentVo vo = (AttachmentVo) bizModel;
        try {
    		String query = "UPDATE T_ATTACHMENT "
    			+ "SET ATTACHMENT_DESC=?, CONTENT_TYPE=?, FILE_NAME=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE ";
    		if(vo.getAttachment() == null){
    			query += ", ATTACHMENT=EMPTY_BLOB() ";
    		}
    		query += "WHERE COMPANY_CODE=? AND LINE_NO=? ";
        	pStmt = conn.prepareStatement(query);
    		int i = 1;
    		setNullableString(pStmt, i++, vo.getAttachmentDescription());
    		setNullableString(pStmt, i++, vo.getAttachmentContentType());
    		setNullableString(pStmt, i++, vo.getAttachmentFileName());
    		setNullableString(pStmt, i++, vo.getUpdateUser());
    		pStmt.setString(i++, vo.getCompanyCode());
    		pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
            
    		int count = pStmt.executeUpdate();
            if (count != 1) {                
                throw new SQLException("Unable to update " + vo.getPrimaryKey());
            }

            if(vo.getAttachment()!=null){
                query = "select ATTACHMENT from T_ATTACHMENT where COMPANY_CODE=? AND LINE_NO=?";
                pStmt = conn.prepareStatement(query);
        		pStmt.setString(1, vo.getCompanyCode());
        		pStmt.setInt(2, vo.getAttachmentAttachmentLineNo().intValue());
    			rs = pStmt.executeQuery();
    			if(rs.next()){
        			BLOB blob=(BLOB) rs.getBlob("ATTACHMENT");  
        			OutputStream out=blob.getBinaryOutputStream();  
        			try {
        				out.write(vo.getAttachment());
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
        			try {
        				out.close();
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
    			}else{
                    throw new SQLException("Unable to update " + vo.getPrimaryKey());
    			}
            }
        } catch (SQLException sqe) {
            throw new ModifyRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }

	/**
	* Constructs a string of SQL delete statement
	* @return        a string of SQL update statement
	*
	* @author     Jimmy chen 
	* @since      Framework 1.0
	*/
	protected String sqlDelete() {
		return null;
	}

	protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException {
	}

	public void remove(SimpleBaseVo bizModel, Connection conn) throws DeleteRecordException {
		removeAttachment(bizModel, conn);
    }


    public void removeAttachment(SimpleBaseVo bizModel, Connection conn) throws DeleteRecordException {
    	AttachmentVo vo = (AttachmentVo) bizModel;
    	PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
    		String query = "DELETE FROM T_ATTACHMENT WHERE COMPANY_CODE=? AND LINE_NO=? ";
            pStmt = conn.prepareStatement(query);
    		int i = 1;
    		pStmt.setString(i++, vo.getCompanyCode());
    		pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
            int count = pStmt.executeUpdate();
            if (count != 1) {
                Logger.warn("Execute Update record count != 1: " + count);
            }
        } catch (SQLException sqe) {
            throw new DeleteRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Jimmy chen 
	* @since      Framework 1.0
	*/
	protected String sqlFind() {
		String sql = "SELECT COMPANY_CODE, LINE_NO, CONTENT_TYPE, FILE_NAME, ATTACHMENT_DESC, "
			+ "ATTACHMENT, DC_IND, DC_DATE, CREATE_USER, CREATE_DATE, "
			+ "UPDATE_USER, UPDATE_DATE "
			+ "FROM T_ATTACHMENT "
			+ "WHERE COMPANY_CODE=? AND LINE_NO=? "
			+ "ORDER BY COMPANY_CODE, LINE_NO";
		return sql;
	}

	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {
		AttachmentPk pk = (AttachmentPk) obj;
		int i = 1;
		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setInt(i++, pk.getAttachmentAttachmentLineNo().intValue());
	}

    public SimpleBaseVo findByPrimaryKey(Object obj, Connection conn) throws FindRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        SimpleBaseVo model = null;
        SimpleBasePk pk = (SimpleBasePk) obj;

        try {
        	long hdrstart = System.currentTimeMillis();
        	
        	String query = sqlFind();
            super.logSqlQuery(query);
            pStmt = conn.prepareStatement(query);
            setValuesForFind(pStmt, pk);

            rs = pStmt.executeQuery();
            boolean modelNotNull = false;

            if (rs.next()) {
                model = (SimpleBaseVo) getResultSetValuesForFind(rs);                
                modelNotNull = true;
            }
            
            if(modelNotNull){
            	long hdrend = System.currentTimeMillis();
                Logger.debug(this, "findByPrimaryKey(), Load header, "
                		+ model.getClass().getName()	
                		+ ", in " + ((hdrend-hdrstart)/100.000) + " seconds");
            }
                        
        } catch (SQLException sqe) {
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(this, sqe);
            }
        }
        return model;
    }

    /**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Jimmy chen 
	* @since      Framework 1.0
	*/
	protected String sqlList() {
		String sql = "SELECT COMPANY_CODE, LINE_NO, CONTENT_TYPE, FILE_NAME, ATTACHMENT_DESC, "
					+ "ATTACHMENT, DC_IND, DC_DATE, CREATE_USER, CREATE_DATE, "
					+ "UPDATE_USER, UPDATE_DATE ";
		String[] keyFields = this.getKeyFields();
		if(keyFields==null || keyFields.length==0){
			return null;
		}
		sql += "FROM (";
		List unions = this.getUnionTableFields();
		if(unions==null || unions.size()==0){
			return null;
		}
		for(int i=0; i<unions.size(); i++){
			String[] tableFiled = (String[]) unions.get(i);
			if(tableFiled==null || tableFiled.length!=2){
				return null;
			}
			if(i>0){
				sql += " UNION ALL ";
			}
			sql += "SELECT B.COMPANY_CODE, B.LINE_NO, B.CONTENT_TYPE, B.FILE_NAME, B.ATTACHMENT_DESC, "
				+ "B.ATTACHMENT, B.DC_IND, B.DC_DATE, B.CREATE_USER, B.CREATE_DATE, "
				+ "B.UPDATE_USER, B.UPDATE_DATE "
				+ "FROM " + CommonUtil.nullToString(tableFiled[0]) + " A, T_ATTACHMENT B "
				+ "WHERE A.COMPANY_CODE=B.COMPANY_CODE "
				+ "AND A." + CommonUtil.nullToString(tableFiled[1]) + "=B.LINE_NO ";
			for(int j=0; j<keyFields.length; j++){
				sql += "AND A."+ CommonUtil.nullToString(keyFields[j]) + "=? ";
			}
		}
		sql += ") ";
		sql += "ORDER BY COMPANY_CODE, LINE_NO";
		return sql;
	}


	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Jimmy chen 
	* @since      Framework 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
		AttachmentVo vo;
		try {
			vo = (AttachmentVo) getAttachmentVoClass().newInstance();
			vo.setCompanyCode(rs.getString("COMPANY_CODE"));
			vo.setAttachmentAttachmentLineNo(CustomBigInteger.customize(rs.getBigDecimal("LINE_NO")));
			vo.setAttachmentDescription(rs.getString("ATTACHMENT_DESC"));
			vo.setAttachmentContentType(rs.getString("CONTENT_TYPE"));
			vo.setAttachmentDcInd(rs.getString("DC_IND"));
			vo.setAttachmentDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));
			vo.setCreateUser(rs.getString("CREATE_USER"));
			vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
			vo.setUpdateUser(rs.getString("UPDATE_USER"));
			vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
			vo.setAttachment(CustomBlob.customize(rs.getBlob("ATTACHMENT")));
			vo.setAttachmentFileName(rs.getString("FILE_NAME"));
		} catch (InstantiationException e) {
			Logger.logExceptions(this, e);
			throw new SQLException(e.getMessage());
		} catch (IllegalAccessException e) {
			throw new SQLException(e.getMessage());
		}
		return vo;
	}

	protected abstract Class getAttachmentVoClass();

	protected abstract List getUnionTableFields();
	
	protected abstract String[] getKeyFields();
	
}
